package com.cancan.demo;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.List;
import java.util.Map;

public class TestExcelGetImage {

    public static void main(String[] args) throws IOException {

//        FileInputStream fis = new FileInputStream("C:\\Users\\anika\\Desktop\\1234.xlsx");
//
//        XSSFWorkbook workbook = new XSSFWorkbook(fis);
//
//        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
//            Sheet sheet = workbook.getSheetAt(i);
//            sheet.getDrawingPatriarch().get
//        }




//        ExcelReader reader = ExcelUtil.getReader("C:\\Users\\anika\\Desktop\\1234.xlsx","sheet1");
//        List<List<Object>> readAll = reader.read();
//
//        Sheet sheet = reader.getSheet();
//
//        for (Row cells : sheet) {
//            for (Cell cell : cells) {
//                if(cell.getCellType() == CellType..PICTURE){
//
//                }
//            }
//        }
//        String file = "C:\\Users\\anika\\Desktop\\1234.xlsx";
        String file = "D:\\共享\\晋泽员工照片.xlsx";

        ExcelReader excelReader = ExcelUtil.getReader(file);

        List<List<Object>> read = excelReader.read();
        System.out.println(read);

        Map<Integer, List<ExcelPicUtil.ExcelPic>> excelPicMap = ExcelPicUtil.getExcelPic(excelReader, excelPic -> {
            // todo ...... 自己实现读取到这个图片时做啥
           // byte[] data = excelPic.getData();
            //BufferedImage bufferedImage = ImgUtil.toImage(data);
        });

        String rootPath = "D:\\ouput\\";
//        excelPicMap.values().forEach(excelPics -> excelPics.forEach(excelPic -> {
//            ExcelPicUtil.writePicByteTo(excelPic.getData(), rootPath + excelPic.getRowIdx() + "-" + excelPic.getColIdx() + "."  + excelPic.getSuffix());
//        }));

        for (List<ExcelPicUtil.ExcelPic> excelPics : excelPicMap.values()) {
            for (ExcelPicUtil.ExcelPic excelPic : excelPics) {
                try {
                    ExcelPicUtil.writePicByteTo(excelPic.getData(), rootPath + read.get(excelPic.getRowIdx()).get(excelPic.getColIdx()+1) + "."  + excelPic.getSuffix());
                }catch (Exception ex){

                }
            }
        }

        System.out.println(excelPicMap.size());



//        File file = new File("C:\\Users\\anika\\Desktop\\1234.xlsx");
//        try {
//            read2(file);
//        } catch (Exception e) {
//            throw new RuntimeException(e);
//        }
    }

    public static void read(File file) throws Exception {
        XSSFWorkbook book = new XSSFWorkbook(new FileInputStream(file));
        //方式1 获取sheet数量，采用下标方式遍历读取每个工作表数据
        int sheetsNos = book.getNumberOfSheets();
        for (int sheetNo = 0; sheetNo < sheetsNos; sheetNo++) {
            //XSSFSheet sheet = book1.getSheetAt(sheetNo);
            Sheet sheet = book.getSheetAt(sheetNo);
        }
        //方式2 获取sheet数量，直接遍历读取每个工作表数据
        for (Sheet sheet : book) {
            XSSFSheet xssSheet = (XSSFSheet) sheet;
            //获取工作表中绘图包
            XSSFDrawing drawing = xssSheet.getDrawingPatriarch();
            if (drawing != null) {
                //获取所有图像形状
                List<XSSFShape> shapes = drawing.getShapes();
                if (shapes != null) {
                    //遍历所有形状
                    for (XSSFShape shape : shapes) {
                        //获取形状在工作表中的顶点位置信息（anchor锚点）
                        XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                        if (shape instanceof XSSFPicture) {
                            //形状获取对应的图片数据
                            XSSFPicture pic = (XSSFPicture) shape;
                            XSSFPictureData picData = pic.getPictureData();
                            //图片形状在工作表中的位置, 所在行列起点和终点位置
                            short c1 = anchor.getCol1();
                            //short c2 = anchor.getCol2();
                            int r1 = anchor.getRow1();
                            //int r2 = anchor.getRow2();
                            String key = r1 + "行," + c1 + "列";
                            //TODO 此处可以将图片位置和数据存入缓存中，以便解析表格数据进行对应操作及保存
                            //保存图片到本地
                            byte[] data = picData.getData();
                            //文件扩展名
                            String suffix = picData.suggestFileExtension();
                            File dir = new File("C:/Users/Administrator/Desktop/excel/img2007/");
                            if (!dir.exists()) {
                                dir.mkdirs();
                            }
                            FileUtils.writeByteArrayToFile(new File(dir.getPath() + "/" + key + "." + suffix), data);
                        }
                    }
                }
            }
        }
    }

    public static void read2(File file) throws Exception {
        XSSFWorkbook book = new XSSFWorkbook(new FileInputStream(file));
        //方式1 获取sheet数量，采用下标方式遍历读取每个工作表数据
        int sheetsNos = book.getNumberOfSheets();
        for (int sheetNo = 0; sheetNo < sheetsNos; sheetNo++) {
            //XSSFSheet sheet = book1.getSheetAt(sheetNo);
            Sheet sheet = book.getSheetAt(sheetNo);
        }
        //方式2 获取sheet数量，直接遍历读取每个工作表数据
        for (Sheet sheet : book) {
            XSSFSheet xssSheet = (XSSFSheet) sheet;
            List<POIXMLDocumentPart> list = xssSheet.getRelations();
            for (POIXMLDocumentPart part : list) {
                if (part instanceof XSSFDrawing) {
                    //获取工作表中绘图包
                    XSSFDrawing drawing = (XSSFDrawing) part;
                    if (drawing != null) {
                        //获取所有图像形状
                        List<XSSFShape> shapes = drawing.getShapes();
                        if (shapes != null) {
                            //遍历所有形状
                            for (XSSFShape shape : shapes) {
                                //获取形状在工作表中的顶点位置信息（anchor锚点）
                                XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                                if (shape instanceof XSSFPicture) {
                                    //形状获取对应的图片数据
                                    XSSFPicture pic = (XSSFPicture) shape;
                                    XSSFPictureData picData = pic.getPictureData();
                                    //图片形状在工作表中的位置, 所在行列起点和终点位置
                                    short c1 = anchor.getCol1();
                                    //short c2 = anchor.getCol2();
                                    int r1 = anchor.getRow1();
                                    //int r2 = anchor.getRow2();
                                    String key = r1 + "行," + c1 + "列";
                                    //TODO 此处可以将图片位置和数据存入缓存中，以便解析表格数据进行对应操作及保存
                                    //保存图片到本地
                                    byte[] data = picData.getData();
                                    //文件扩展名
                                    String suffix = picData.suggestFileExtension();
                                    File dir = new File("C:/Users/Administrator/Desktop/excel/img2007/");
                                    if (!dir.exists()) {
                                        dir.mkdirs();
                                    }
                                    FileUtils.writeByteArrayToFile(new File(dir.getPath() + "/" + key + "." + suffix), data);
                                }
                            }
                        }
                    }
                }
            }
        }
    }

//    public static void read3(File file) throws Exception {
//        //包管理工具打开压缩包
//        OPCPackage opc = PackageHelper.open(new FileInputStream(file));
//        //获取所有包文件
//        List<PackagePart> parts = opc.getParts();
//        //获取每个工作表中的包文件
//        Map<Integer, List<PackagePart>> picturePath = getEmbedPictures(parts);
//        for (Integer key : picturePath.keySet()) {
//            List<PackagePart> rows = picturePath.get(key);
//            for (int i = 0; i < rows.size(); i++) {
//                PackagePart part = rows.get(i);
//                //System.out.println("sheetNo" + key + "\t第" + i + "行\t" + part);
//                if (part != null) {
//                    InputStream imgIs = part.getInputStream();
//                    String name = part.getPartName().getName();
//
//                    File dir = new File("C:/Users/Administrator/Desktop/excel/img2007_embed/");
//                    if (!dir.exists()) {
//                        dir.mkdirs();
//                    }
//                    FileUtils.copyInputStreamToFile(imgIs, new File(dir.getPath() + "/工作表" + key + "," + i + "行_" + name.substring(name.lastIndexOf("/") + 1)));
//                }
//            }
//        }
//        try {
//            opc.close();
//        } catch (NullPointerException | IOException e) {
//
//        }
//    }
//
//    private static Map<Integer, List<PackagePart>> getEmbedPictures(List<PackagePart> parts) throws JDOMException, IOException, ParserConfigurationException, SAXException {
//        Map<String, Set<String>> mapImg = new HashMap<>();
//        Map<String, String> mapImgPath = new HashMap<>();
//        Map<Integer, List<String>> dataMap = new HashMap<>();
//
//        for (PackagePart part : parts) {
////            System.out.println(part.getPartName());
//            PackagePartName partName = part.getPartName();
//            String name = partName.getName();
//            if ("/xl/cellimages.xml".equals(name)) {
//                SAXBuilder builder = new SAXBuilder();
//                // 获取文档
//                Document doc = builder.build(part.getInputStream());
//                // 获取根节点
//                Element root = doc.getRootElement();
//                List<Element> cellImageList = root.getChildren();
//                for (Element imgEle : cellImageList) {
//                    Element xdrPic = imgEle.getChildren().get(0);
//                    Element xdrNvPicPr = xdrPic.getChildren().get(0);
//                    Element xdrBlipFill = xdrPic.getChildren().get(1);
//                    Element aBlip = xdrBlipFill.getChildren().get(0);
//                    Attribute attr = aBlip.getAttributes().get(0);
//                    String imgId = xdrNvPicPr.getChildren().get(0).getAttributeValue("name");
//                    String id = attr.getValue();
////                    if (id.equals("rId12")) {
////                        System.out.println(attr.getValue() + "\t" + imgId);
////                    }
//                    if (mapImg.containsKey(id)) {
//                        mapImg.get(id).add(imgId);
//                    } else {
//                        Set<String> set = new HashSet<>();
//                        set.add(imgId);
//                        mapImg.put(id, set);
//                    }
//                }
//            }
//
//            if ("/xl/_rels/cellimages.xml.rels".equals(name)) {
//                SAXBuilder builder = new SAXBuilder();
//                // 获取文档
//                Document doc = builder.build(part.getInputStream());
//                // 获取根节点
//                Element root = doc.getRootElement();
//                List<Element> relationshipList = root.getChildren();
//                /*
//                  <Relationship Id="rId999" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="media/image1000.jpeg"/>
//                 */
//                for (Element relationship : relationshipList) {
//                    String id = relationship.getAttributeValue("Id");
//                    String target = relationship.getAttributeValue("Target");
//                    mapImgPath.put(id, target);
////                    if (id.equals("rId12")) {
////                        System.out.println(id + "\t" + target);
////                    }
//                }
//            }
//
//            if (name.contains("/xl/worksheets/sheet")) {
////                SAXBuilder builder = new SAXBuilder();
//                // 获取文档
//                String sheetNoStr = name.replace("/xl/worksheets/sheet", "").replace(".xml", "");
//                Integer sheetNo = Integer.valueOf(sheetNoStr) - 1;
//                // 步骤1：创建SAXParserFactory实例
//                SAXParserFactory factory = SAXParserFactory.newInstance();
//                // 步骤2：创建SAXParser实例
//                SAXParser parser = factory.newSAXParser();
//                MySAXParserHandler handler = new MySAXParserHandler();
//                parser.parse(part.getInputStream(), handler);
//
//                List<String> rows = handler.getRows();
//
//                dataMap.put(sheetNo, rows);
//            }
//
//        }
//
////        for (Integer sheetNo : dataMap.keySet()) {
////            System.out.println(sheetNo + "\t" + dataMap.get(sheetNo).size());
////        }
//
//        Map<String, String> imgMap = new HashMap<>();
//        for (String id : mapImg.keySet()) {
//            Set<String> imgIds = mapImg.get(id);
//            String path = mapImgPath.get(id);
//            for (String imgId : imgIds) {
//                imgMap.put(imgId, path);
//            }
//        }
//        for (Integer key : dataMap.keySet()) {
//            List<String> rows = dataMap.get(key);
//            for (int i = 0; i < rows.size(); i++) {
//                String imgId = rows.get(i);
//                if (imgMap.containsKey(imgId)) {
//                    rows.set(i, imgMap.get(imgId));
//                }
//            }
//        }
//
//        Map<Integer, List<PackagePart>> map = new HashMap<>();
//        for (Integer key : dataMap.keySet()) {
//            List<PackagePart> list = new ArrayList<>();
//            map.put(key, list);
//            List<String> pathList = dataMap.get(key);
//            for (int i = 0; i < pathList.size(); i++) {
//                list.add(i, null);
//                String path = pathList.get(i);
//                if (!StringUtils.isEmpty(path)) {
//                    for (PackagePart part : parts) {
//                        PackagePartName partName = part.getPartName();
//                        String name = partName.getName();
//                        // /xl/media/image373.jpeg = media/image702.jpeg
//                        if (name.contains(path)) {
//                            list.set(i, part);
//                            break;
//                        }
//                    }
//                }
//
//            }
//        }
//        return map;
//    }



}
